package com.coai.message.repository

import com.coai.message.model.Message
import com.coai.message.model.MessageType
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository
import java.time.LocalDateTime

@Repository
interface MessageRepository : JpaRepository<Message, String> {
    
    fun findByConversationIdOrderByCreatedAtDesc(
        conversationId: String,
        pageable: Pageable
    ): List<Message>
    
    @Query("""
        SELECT m FROM Message m
        WHERE m.conversationId = :conversationId
        AND m.createdAt < :beforeTime
        AND m.deletedAt IS NULL
        ORDER BY m.createdAt DESC
    """)
    fun findByConversationIdBeforeTime(
        conversationId: String,
        beforeTime: LocalDateTime,
        pageable: Pageable
    ): List<Message>
    
    @Query("""
        SELECT m FROM Message m
        WHERE m.conversationId = :conversationId
        AND m.messageType IN :types
        AND m.deletedAt IS NULL
        ORDER BY m.createdAt DESC
    """)
    fun findByConversationIdAndTypes(
        conversationId: String,
        types: List<MessageType>,
        pageable: Pageable
    ): List<Message>
    
    @Query("""
        SELECT COUNT(m) FROM Message m
        WHERE m.conversationId = :conversationId
        AND m.senderId != :userId
        AND m.createdAt > (
            SELECT COALESCE(MAX(mrs.readAt), '1970-01-01')
            FROM MessageReadStatus mrs
            WHERE mrs.userId = :userId
            AND EXISTS (
                SELECT 1 FROM Message m2
                WHERE m2.id = mrs.messageId
                AND m2.conversationId = :conversationId
            )
        )
    """)
    fun countUnreadMessages(conversationId: String, userId: String): Long
    
    @Query("""
        SELECT m FROM Message m
        WHERE m.conversationId = :conversationId
        AND (LOWER(m.content) LIKE LOWER(CONCAT('%', :keyword, '%')))
        AND m.deletedAt IS NULL
        ORDER BY m.createdAt DESC
    """)
    fun searchByKeyword(
        conversationId: String,
        keyword: String,
        pageable: Pageable
    ): List<Message>
}
